package com.erp.app.common.select.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.Map;

import com.erp.framework.exceptions.CreateRecordException;
import com.erp.framework.exceptions.DeleteRecordException;
import com.erp.framework.exceptions.FindRecordException;
import com.erp.framework.exceptions.ModifyRecordException;
import com.erp.framework.model.dao.SimpleBaseDao;
import com.erp.framework.model.vo.ChildList;
import com.erp.framework.model.vo.SimpleBaseVo;
import com.erp.framework.types.CustomBlob;
import com.erp.framework.util.DbObjectUtil;
import com.erp.framework.util.DbObjectUtil.ResultSetInterpreter;
import com.erp.framework.util.apaches.commons.collection.map.LinkedMap;
import com.erp.framework.util.logger.Logger;

//import com.erp.app.modules.salesContract.salesContractPackset.beans.SalesContractPacksetVO;

public class SelectDAO extends SimpleBaseDao {

    public Map selectMap(String query, Connection conn) throws FindRecordException {
        Map result = new LinkedMap();
        Logger.sql(this, query);
        PreparedStatement statement = null;
        ResultSet rs = null;
        DbObjectUtil util = new DbObjectUtil();
        try {
            statement = conn.prepareStatement(query);
            rs = statement.executeQuery();
            ResultSetMetaData md = rs.getMetaData();
            if (md.getColumnCount() < 2) {
                throw new FindRecordException("Number of column selected must be at least 2.");
            }
            ResultSetInterpreter keyColumnInterpretor = util.getResultSetInterpreter(md.getColumnType(1));
            ResultSetInterpreter valueColumnInterpretor = util.getResultSetInterpreter(md.getColumnType(2));
            while (rs.next()) {
                result.put(keyColumnInterpretor.interpret(rs, 1), valueColumnInterpretor.interpret(rs, 2));
            }
        } catch (SQLException e) {
            Logger.error(this, e);
            throw new FindRecordException(e.getMessage());
        } finally {
            try {
                if (rs != null) rs.close();
                if (statement != null) statement.close();
            } catch (SQLException e) {}
        }
        return result;
    }

    public ArrayList findDateFields(String table, String column, String where, Connection conn) {
        ArrayList resultList = new ArrayList();
        Statement stmt = null;
        ResultSet rs = null;
        String query = "SELECT " + column + " FROM " + table + ((where != null && !where.equals("")) ? " WHERE " + where : "");

        Logger.sql(this, "findDateFields(), sql=" + query);

        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery(query);
            while (rs != null && rs.next()) {
                Object o = null;
                o = rs.getDate(1);
                if (o == null) {
                    resultList.add(new Object());
                } else {
                    resultList.add(o);
                }
            }
        } catch (Exception e) {
        	Logger.logExceptions(this, e);
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (stmt != null)
                    stmt.close();
            } catch (Exception e) {
            }
        }
        return resultList;
    }

    public ArrayList findFields(String table, String column, String where, Connection conn) {
        ArrayList resultList = new ArrayList();
        Statement stmt = null;
        ResultSet rs = null;
        String query = "SELECT " + column + " FROM " + table + ((where != null && !where.equals("")) ? " WHERE " + where : "");

        Logger.sql(this, "findFields(), sql=" + query);

        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery(query);
            while (rs != null && rs.next()) {
                Object o = null;
                o = rs.getObject(1);
                // if(o == null) {
                // resultList.add(new Object());
                // } else {
                // resultList.add(o);
                // }
                if (o != null)
                    resultList.add(o);
            }
        } catch (Exception e) {
        	Logger.logExceptions(this, e);
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (stmt != null)
                    stmt.close();
            } catch (Exception e) {
            }
        }
        return resultList;
    }

    public Object selectOneValue(String query, Connection conn) {
        Object obj = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
        	Logger.sql("select(), query=" + query);
            stmt = conn.createStatement();
            rs = stmt.executeQuery(query);
            if (rs != null && rs.next()) {
                Object o = null;
                o = rs.getObject(1);
                if (o == null) {
                    obj = null;
                } else {
                    obj = o;
                }
            }
        } catch (Exception e) {
            Logger.logExceptions(this, e);
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (stmt != null)
                    stmt.close();
            } catch (Exception e) {
            }
        }
        return obj;
    }

    public byte[] selectOneImage(String query, Connection conn) {        
        Statement stmt = null;
        ResultSet rs = null;
        byte[] image = null;

        try {
        	Logger.sql("select(), query=" + query);
            stmt = conn.createStatement();
            rs = stmt.executeQuery(query);
            if (rs != null && rs.next()) {
                byte[] i = null;
                i = rs.getBytes(1);
                if (i == null) {
                    image = null;
                } else {
                    image = i;
                }
            }
        } catch (Exception e) {
            Logger.logExceptions(this, e);
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (stmt != null)
                    stmt.close();
            } catch (Exception e) {
            }
        }
        return image;
    }
    
    public byte[] selectOneBlobImage(String query, Connection conn) {        
        Statement stmt = null;
        ResultSet rs = null;
        byte[] image = null;

        try {
        	Logger.sql("select(), query=" + query);
            stmt = conn.createStatement();
            rs = stmt.executeQuery(query);
            if (rs != null && rs.next()) {
                byte[] i = null;
                i = CustomBlob.customize(rs.getBlob(1));
                if (i == null) {
                    image = null;
                } else {
                    image = i;
                }
            }
        } catch (Exception e) {
            Logger.logExceptions(this, e);
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (stmt != null)
                    stmt.close();
            } catch (Exception e) {
            }
        }
        return image;
    }
    
    public ArrayList selectList(String query, Connection conn) {
        ArrayList rslt = new ArrayList();
        ArrayList valueList = new ArrayList();
        Statement stmt = null;
        ResultSet rs = null;
        ResultSetMetaData md = null;

        Logger.sql(this, "selectList(), sql=" + query);

        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery(query);
            md = rs.getMetaData();
            int noCols = md.getColumnCount();

            if (rs != null) {
                while ((rs != null) && (rs.next())) {
                	
                    for (int i = 1; i < (noCols + 1); i++) {
                        int type = md.getColumnType(i);
                        String colName = md.getColumnName(i);
                        Object obj = null;

                        switch (type) {
                            case java.sql.Types.BIGINT:
                                obj = rs.getBigDecimal(i);
                                break;
                            case java.sql.Types.CHAR:
                                obj = rs.getString(i);
                                break;
                            case java.sql.Types.DATE:
                                obj = rs.getDate(i);
                                break;
                            case java.sql.Types.DECIMAL:
                                obj = rs.getBigDecimal(i);
                                break;
                            case java.sql.Types.DOUBLE:
                                obj = new Double(rs.getDouble(i));
                                break;
                            case java.sql.Types.FLOAT:
                                obj = new Float(rs.getFloat(i));
                                break;
                            case java.sql.Types.INTEGER:
                                obj = new Integer(rs.getInt(i));
                                break;
                            case java.sql.Types.NUMERIC:
                                obj = rs.getBigDecimal(i);
                                break;
                            case java.sql.Types.TIMESTAMP:
                                obj = rs.getTimestamp(i);
                                break;
                            case java.sql.Types.REAL:
                                obj = rs.getBigDecimal(i);
                                break;
                            case java.sql.Types.VARCHAR:
                                obj = rs.getString(i);
                                break;
                        }

                        if (obj != null) {
                        	valueList.add(obj);
                        } else {
                        	valueList.add(null);
                        }
                    }
                    rslt.add(valueList);
                    valueList = new ArrayList();
                }
            }
        } catch (Exception e) {
            Logger.logExceptions(this, e);
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (stmt != null)
                    stmt.close();
            } catch (Exception e) {
            	Logger.logExceptions(this, e);
            }
        }
        return rslt;
    }

    public ArrayList selectStringList(String query, Connection conn) {
        ArrayList rslt = new ArrayList();
        Statement stmt = null;
        ResultSet rs = null;

        Logger.sql(this, "selectList(), sql=" + query);

        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery(query);
            
            if (rs != null) {
                while ((rs != null) && (rs.next())) {
                	
                    String str = rs.getString(1);
                    rslt.add(str);
                }
            }
        } catch (Exception e) {
            Logger.logExceptions(this, e);
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (stmt != null)
                    stmt.close();
            } catch (Exception e) {
            	Logger.logExceptions(this, e);
            }
        }
        return rslt;
    }
    
    public ArrayList selectOneList(String query, Connection conn) {
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList rsltList = new ArrayList();

        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery(query);
            while ((rs != null) && (rs.next())) {
                Object o = null;
                o = rs.getObject(1);
                rsltList.add(o);
            }
        } catch (Exception e) {
        	System.out.println( e);
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (stmt != null)
                    stmt.close();
            } catch (Exception e) {
            }
        }
        return rsltList;
    }

    public Object findField(String table, String column, String where, Connection conn) {
        Object obj = null;
        Statement stmt = null;
        ResultSet rs = null;
        String query = "SELECT " + column + " FROM " + table + ((where != null && !where.equals("")) ? " WHERE " + where : "");
        Logger.sql(this, "findField(), sql=" + query);

        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery(query);
            if (rs != null && rs.next()) {
                Object o = null;
                o = rs.getObject(1);
                if (o == null) {
                    obj = null;
                } else {
                    obj = o;
                }
            }
        } catch (Exception e) {
        	Logger.logExceptions(this, e);
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (stmt != null)
                    stmt.close();
            } catch (Exception e) {
            }
        }
        return obj;
    }

    public ArrayList findFieldList(String table, String[] columns, String where, Connection conn) {
        ArrayList rslt = new ArrayList();
        Hashtable hash = new Hashtable();
        Statement stmt = null;
        ResultSet rs = null;
        ResultSetMetaData md = null;

        // System.out.println("DAO, columns size=" + columns.length);

        String column = "";
        for (int i = 0; i < columns.length; i++) {
            column = column + columns[i] + ",";
        }
        // System.out.println("COLUMNS=" + column);
        String columnNames = column.substring(0, column.length() - 1);
        // System.out.println("columnNames=" + columnNames);

        String query = "SELECT " + columnNames + " FROM " + table
                + ((where != null && !where.equals("")) ? " WHERE " + where : "");

        Logger.sql(this, "findFieldList(), sql=" + query);

        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery(query);
            md = rs.getMetaData();
            // int noCols = md.getColumnCount();
            int noCols = columns.length;

            if (rs != null) {
                while ((rs != null) && (rs.next())) {
                    for (int i = 1; i < (noCols + 1); i++) {
                        int type = md.getColumnType(i);
                        String colName = md.getColumnName(i);
                        Object obj = null;

                        switch (type) {
                            case java.sql.Types.BIGINT:
                                // System.out.println("DAO, TYPE=" +
                                // java.sql.Types.BIGINT + ", type=" + type);
                                obj = rs.getBigDecimal(i);
                                break;
                            case java.sql.Types.CHAR:
                                // System.out.println("DAO, TYPE=" +
                                // java.sql.Types.CHAR + ", type=" + type);
                                obj = rs.getString(i);
                                break;
                            case java.sql.Types.DATE:
                                // System.out.println("DAO, TYPE=" +
                                // java.sql.Types.DATE + ", type=" + type);
                                obj = rs.getDate(i);
                                break;
                            case java.sql.Types.DECIMAL:
                                // System.out.println("DAO, TYPE=" +
                                // java.sql.Types.DECIMAL + ", type=" + type);
                                obj = rs.getBigDecimal(i);
                                break;
                            case java.sql.Types.DOUBLE:
                                // System.out.println("DAO, TYPE=" +
                                // java.sql.Types.DOUBLE + ", type=" + type);
                                obj = new Double(rs.getDouble(i));
                                break;
                            case java.sql.Types.FLOAT:
                                // System.out.println("DAO, TYPE=" +
                                // java.sql.Types.FLOAT + ", type=" + type);
                                obj = new Float(rs.getFloat(i));
                                break;
                            case java.sql.Types.INTEGER:
                                // System.out.println("DAO, TYPE=" +
                                // java.sql.Types.INTEGER + ", type=" + type);
                                obj = new Integer(rs.getInt(i));
                                break;
                            case java.sql.Types.NUMERIC:
                                // System.out.println("DAO, TYPE=" +
                                // java.sql.Types.NUMERIC + ", type=" + type);
                                obj = rs.getBigDecimal(i);
                                break;
                            case java.sql.Types.TIMESTAMP:
                                // System.out.println("DAO, TYPE=" +
                                // java.sql.Types.TIMESTAMP + ", type=" + type);
                                obj = rs.getTimestamp(i);
                                break;
                            case java.sql.Types.REAL:
                                // System.out.println("DAO, TYPE=" +
                                // java.sql.Types.REAL + ", type=" + type);
                                obj = rs.getBigDecimal(i);
                                break;
                            case java.sql.Types.VARCHAR:
                                // System.out.println("DAO, TYPE=" +
                                // java.sql.Types.VARCHAR + ", type=" + type);
                                obj = rs.getString(i);
                                break;
                            default:
                            	obj = "";
                            	break;
                        }

                        if (obj != null) {
                            hash.put(colName, obj);
                        } else {
                            hash.put(colName, "");
                        	//hash.put(colName, null);
                        }
                    }
                    rslt.add(hash);
                    hash = new Hashtable();
                }
            }
        } catch (Exception e) {
        	Logger.logExceptions(this, e);
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (stmt != null)
                    stmt.close();
            } catch (Exception e) {
            }
        }
        return rslt;
    }
    
    public ArrayList findAllMenuIds(String roleGroupId, String companyCode, Connection conn) throws FindRecordException {
        ArrayList rslt = new ArrayList();
        Statement stmt = null;
        ResultSet rs = null;
        String query = "SELECT A.MENU_ID" + " FROM T_MENU_GROUP A, T_COMPANY_MENU_GROUP B" + " WHERE A.MENU_TYPE='M'"
                + " AND A.COMPANY_CODE=B.COMPANY_CODE" + " AND A.GROUP_ID=B.GROUP_ID" + " AND A.MENU_ID=B.MENU_ID"
                + " AND B.ROLE_GROUP_ID='" + roleGroupId + "'"
                + " AND B.COMPANY_CODE='" + companyCode + "'"
                
                + " ORDER BY A.MENU_ORDER";
        
        Logger.sql(this, "findAllMenuIds(), " + query);
        
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery(query);
            if (rs != null) {
                while (rs.next()) {
                    if (rs.getString(1) != null) {
                        rslt.add(rs.getString(1));
                    } else {
                        rslt.add(new String());
                    }
                }
            }
        } catch (Exception e) {
        	Logger.logExceptions(this, e);
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (stmt != null)
                    stmt.close();
            } catch (Exception e) {
            }
        }
        return rslt;
    }

    public ArrayList findUserGroupInfo(String compCode, String userGroupCode, String userCode, Connection conn)
            throws FindRecordException {
        ArrayList rslt = new ArrayList();
        Hashtable hash = new Hashtable();
        Statement stmt = null;
        ResultSet rs = null;
        String query = "SELECT b.user_code USER_CODE, b.user_group_code USER_GROUP_CODE,"
                + " 'TASK' TASK_DOC, 'ACTIVE' STATUS, a.is_active IS_ACTIVE" + " FROM t_user b"
                + " WHERE "
                + " AND B.COMPANY_CODE = '" + compCode + "'"
                + " AND b.user_code = '" + userCode + "'";

        super.logSqlQuery(query);

        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery(query);
            if (rs != null) {
                while (rs.next()) {
                    String field1 = rs.getString(1);
                    String field2 = rs.getString(2);
                    String field3 = rs.getString(3);
                    String field4 = rs.getString(4);
                    String field5 = rs.getString(5);

                    if (field1 != null) {
                        hash.put("USER_CODE", field1);
                    } else {
                        hash.put("USER_CODE", new String());
                    }
                    if (field2 != null) {
                        hash.put("USER_GROUP_CODE", field2);
                    } else {
                        hash.put("USER_GROUP_CODE", new String());
                    }
                    if (field3 != null) {
                        hash.put("TASK_DOC", field3);
                    } else {
                        hash.put("TASK_DOC", new String());
                    }
                    if (field4 != null) {
                        hash.put("STATUS", field4);
                    } else {
                        hash.put("STATUS", new String());
                    }
                    if (field5 != null) {
                        hash.put("IS_ACTIVE", field5);
                    } else {
                        hash.put("IS_ACTIVE", new String());
                    }

                    rslt.add(hash);
                    hash = new Hashtable();
                }
            }
        } catch (Exception e) {
            Logger.logExceptions(this, e);
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (stmt != null)
                    stmt.close();
            } catch (Exception e) {
                Logger.logExceptions(this, e);
            }
        }
        return rslt;
    }

    public ChildList selectByParent(SimpleBaseVo parent, Connection conn) throws FindRecordException {
        return null;
    }

    public SimpleBaseVo findByPrimaryKey(Object obj, Connection conn) throws FindRecordException {
        return null;
    }

    public void insert(SimpleBaseVo bizModel, Connection conn) throws CreateRecordException {
    }

    public void update(SimpleBaseVo bizModel, Connection conn) throws ModifyRecordException {
    }

    public void remove(SimpleBaseVo bizModel, Connection conn) throws DeleteRecordException {
    }

}
